package com.nononsenseapps.feeder.db.room

import android.app.Application
import android.content.Context
import android.database.sqlite.SQLiteDatabase
import androidx.room.*
import androidx.room.migration.Migration
import androidx.sqlite.db.SupportSQLiteDatabase
import com.nononsenseapps.feeder.db.*
import com.nononsenseapps.feeder.db.entity.*
import com.nononsenseapps.feeder.db.ext.Converters
import com.nononsenseapps.feeder.db.view.PreviewItem
import com.nononsenseapps.feeder.util.*
import java.util.concurrent.Executors

const val DATABASE_NAME = "rssDatabase"
const val ID_UNSET: Long = 0
const val ID_ALL_FEEDS: Long = -200

const val FAVORITE_FEED_ID : Long= -1
const val RULE_FEED_ID : Long= -2
const val HOT_FEED_ID : Long= -3
const val RECYCLE_FEED_ID: Long = -4
const val STORE_FEED_ID : Long= -5
const val MEMO_FEED_ID : Long= -6
const val BACKUP_FEED_ID : Long= -999
const val READ_LATE_FEED_ID :Long= -7


/**
 * Database versions
 * 4: Was using the RSS Server
 * 5: Added feed url field to feed_item
 * 6: Added feed icon field to feeds
 * 7: Migration to Room
 */

@Database(
    exportSchema = true,
    entities = [Feed::class, FeedItem::class, FeedSyncItem::class, FeedItemFeature::class, FeedRule::class, FeedItemTag::class, FeedItemPreLoadItem::class],
    views = [PreviewItem::class],
    autoMigrations = [
        AutoMigration (from = 24, to = 25),
        AutoMigration (from = 25, to = 26),
        AutoMigration (from = 26, to = 27),
        AutoMigration (from = 27, to = 28),
        AutoMigration (from = 28, to = 29),
        AutoMigration (from = 29, to = 30),
        AutoMigration (from = 30, to = 31)
                     ],
    version = 31)
@TypeConverters(Converters::class)
abstract class AppDatabase : RoomDatabase() {
    abstract fun feedDao(): FeedDao
    abstract fun feedItemDao(): FeedItemDao
    abstract fun feedSyncItemDao(): FeedSyncItemDao
    abstract fun feedItemFeatureDao(): FeedItemFeatureDao
    abstract fun feedRuleDao():FeedRuleDao
    abstract fun feedItemTagDao():FeedItemTagDao
    abstract fun feedItemPreLoadItemDao():FeedItemPreLoadItemDao

    companion object {
        // For Singleton instantiation
        @Volatile
        private var instance: AppDatabase? = null

        /**
         * Use this in tests only
         */
        internal fun setInstance(db: AppDatabase) {
            instance = db
        }

        fun getInstance(context: Context): AppDatabase {
            return instance
                ?: synchronized(this) {
                    instance
                        ?: buildDatabase(context).also { instance = it }
                }
        }

        private fun buildDatabase(context: Context): AppDatabase {
            val dbBuilder = Room.databaseBuilder(context, AppDatabase::class.java, DATABASE_NAME)

             val queryCallback = object : QueryCallback {
                 override fun onQuery(sqlQuery: String, bindArgs: List<Any?>) {
                     println("SQL Query: $sqlQuery SQL Args: $bindArgs")
                 }
             }

            dbBuilder.setQueryCallback(queryCallback = queryCallback, Executors.newSingleThreadExecutor())

            return dbBuilder.addMigrations(*allMigrations).build()
        }

        fun init(context:Application){
            getInstance(context)
        }
    }
}

val allMigrations = arrayOf(MIGRATION_5_7,
        MIGRATION_6_7,
        MIGRATION_7_8,
        MIGRATION_8_9,
        MIGRATION_9_10,
        MIGRATION_10_11,
        MIGRATION_11_12,
        MIGRATION_12_13,
        MIGRATION_13_14,
        MIGRATION_14_15,
        MIGRATION_15_16,
        MIGRATION_16_17,
        MIGRATION_17_18,
        MIGRATION_18_19,
        MIGRATION_19_20,
        MIGRATION_20_21,
        MIGRATION_21_22,
        MIGRATION_22_23,
        MIGRATION_23_24
)

fun installFeed(database: SupportSQLiteDatabase,feed: Feed){
    database.insert("feeds",
            SQLiteDatabase.CONFLICT_FAIL,
            contentValues {
                setLong("id" to HOT_FEED_ID)
                setString("title" to feed.title)
                setString("custom_title" to feed.customTitle)
                setString("url" to feed.url.toString())

            })
}


object MIGRATION_23_24: Migration(23, 24) {
    override fun migrate(database: SupportSQLiteDatabase) {
        database.execSQL("""
             ALTER TABLE feed_rule ADD COLUMN ${FEED_RULE.COL_ACTION} INTEGER NOT NULL DEFAULT 0
            """.trimIndent())
        database.execSQL("""
             ALTER TABLE feed_rule ADD COLUMN ${FEED_RULE.COL_ACTION_FEEDID} INTEGER NOT NULL DEFAULT $RULE_FEED_ID
            """.trimIndent())
        database.execSQL("""
             ALTER TABLE feed_rule ADD COLUMN ${FEED_RULE.COL_ACTION_REPLACE_VALUE} TEXT NOT NULL DEFAULT ""
            """.trimIndent())
        database.execSQL("""
             ALTER TABLE feed_rule ADD COLUMN ${FEED_RULE.COL_RULE_VALUE_IS_REGEX_PATTERN} INTEGER NOT NULL DEFAULT 0
            """.trimIndent())

    }
}

object MIGRATION_22_23: Migration(22, 23) {
    override fun migrate(database: SupportSQLiteDatabase) {
        database.execSQL("""
             ALTER TABLE feed_rule ADD COLUMN ${FEED_RULE.COL_ABLE} INTEGER NOT NULL DEFAULT 1
            """.trimIndent())
    }
}

object MIGRATION_21_22: Migration(21, 22) {
    override fun migrate(database: SupportSQLiteDatabase) {
        database.execSQL("""
            CREATE TABLE IF NOT EXISTS `feed_item_tag` (
                 `id` INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
                 name TEXT NOT NULL DEFAULT "",
                 parent_tag TEXT NOT NULL DEFAULT $FEED_ITEM_TAG_ROOT_NAME,
                 click_count INTEGER NOT NULL DEFAULT 0,
                 hot INTEGER NOT NULL DEFAULT 0,
                 attention_level INTEGER NOT NULL DEFAULT  $ATTENTION_NORMAL
                 )
        """.trimIndent())

        database.execSQL("""
            CREATE UNIQUE INDEX "index_feed_item_tag_name" ON "feed_item_tag" ( "name" ASC )
        """.trimIndent())

    }
}

object MIGRATION_20_21: Migration(20, 21) {
    override fun migrate(database: SupportSQLiteDatabase) {
        database.execSQL("""
             ALTER TABLE feeds ADD COLUMN ${FEED.COL_OVERWRITE} INTEGER NOT NULL DEFAULT 0
            """.trimIndent())
        database.execSQL("""
             ALTER TABLE feeds ADD COLUMN ${FEED.COL_FEED_ITEM_TYPE} INTEGER NOT NULL DEFAULT $ITEM_TYPE_NO_SET
            """.trimIndent())
        database.execSQL("""
             ALTER TABLE feeds ADD COLUMN ${FEED.COL_FEED_ITEM_BODY_FORMAT_TYPE} INTEGER NOT NULL DEFAULT $ITEM_BODY_FORMAT_TYPE_NO_SET
            """.trimIndent())
        database.execSQL("""
             ALTER TABLE feeds ADD COLUMN ${FEED.COL_LAST_SUCCESS_SYNC_TIME} INTEGER NOT NULL DEFAULT 0
            """.trimIndent())
        database.execSQL("""
             ALTER TABLE feed_items ADD COLUMN ${FEED_ITEM.COL_ATTENTION_LEVEL} INTEGER NOT NULL DEFAULT $ATTENTION_NORMAL
            """.trimIndent())
        database.execSQL("""
             ALTER TABLE feed_items ADD COLUMN ${FEED_ITEM.COL_BODY_FORMAT_TYPE} INTEGER NOT NULL DEFAULT $ITEM_BODY_FORMAT_TYPE_NO_SET
            """.trimIndent())
    }
}

object MIGRATION_19_20: Migration(19, 20) {
    override fun migrate(database: SupportSQLiteDatabase) {
        database.execSQL("""
             ALTER TABLE feeds ADD COLUMN backup INTEGER NOT NULL DEFAULT 0
            """.trimIndent())
        database.execSQL("""
             ALTER TABLE feed_items ADD COLUMN word_count INTEGER NOT NULL DEFAULT 0
            """.trimIndent())
    }
}

object MIGRATION_18_19: Migration(18, 19) {
        override fun migrate(database: SupportSQLiteDatabase) {
            database.execSQL("""
              ALTER TABLE feeds ADD COLUMN rule_able INTEGER NOT NULL DEFAULT 0
        """.trimIndent())
            database.execSQL("""
             ALTER TABLE feed_items ADD COLUMN rule_checked INTEGER NOT NULL DEFAULT 0
        """.trimIndent())
            database.execSQL("""
            CREATE TABLE IF NOT EXISTS `feed_rule` (
                 `id` INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
                 title TEXT NOT NULL DEFAULT "",
                 feed_id INTEGER,
                 ad_check INTEGER NOT NULL DEFAULT 0,
                 style INTEGER NOT NULL DEFAULT 0,
                 rule_value TEXT NOT NULL DEFAULT "",
                 FOREIGN KEY ("feed_id") REFERENCES "feeds" ("id") ON DELETE CASCADE ON UPDATE NO ACTION
                 )
        """.trimIndent())
            database.execSQL("""
            CREATE INDEX "index_feed_rule_feed_id" ON "feed_rule" ( "feed_id" ASC )
        """.trimIndent())
        }
}

object MIGRATION_17_18: Migration(17, 18) {
    override fun migrate(database: SupportSQLiteDatabase) {
        database.execSQL("""
             CREATE INDEX "index_feed_items_feature_id" ON "feed_items" ( "item_feature" ASC )
        """.trimIndent())
        database.execSQL("""
             CREATE INDEX "index_feed_sync_item_feed_id" ON "feed_sync_item" ( "feed_id" ASC )
        """.trimIndent())
    }
}

object MIGRATION_16_17: Migration(16, 17) {
    override fun migrate(database: SupportSQLiteDatabase) {
        database.execSQL("""
            ALTER TABLE feeds ADD COLUMN feed_last_sync_success INTEGER NOT NULL DEFAULT 1
        """.trimIndent())
    }
}

object MIGRATION_15_16: Migration(15, 16) {
    override fun migrate(database: SupportSQLiteDatabase) {
        database.execSQL("""
             ALTER TABLE feeds ADD COLUMN attention_level INTEGER NOT NULL DEFAULT $ATTENTION_NORMAL
             """.trimIndent())
    }
}


object MIGRATION_14_15: Migration(14, 15) {
    override fun migrate(database: SupportSQLiteDatabase) {
        database.execSQL("""
            CREATE TABLE IF NOT EXISTS `feed_item_feature` (`id` INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,create_date INTEGER NOT NULL DEFAULT 0,title TEXT NOT NULL DEFAULT "")
        """.trimIndent())
        database.execSQL("""
            ALTER TABLE feed_items ADD COLUMN item_feature INTEGER
        """.trimIndent())
        database.execSQL("""
            DROP INDEX "index_feed_items_feed_id"
        """.trimIndent())
        database.execSQL("""
            DROP INDEX "index_feed_items_guid_feed_id"
        """.trimIndent())
        database.execSQL("""
            ALTER TABLE "feed_items" RENAME TO "_feed_items_old_20200513"
        """.trimIndent())
        database.execSQL("""
            CREATE TABLE "feed_items" (
                "id" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
                "guid" TEXT NOT NULL,
                "title" TEXT NOT NULL,
                "description" TEXT NOT NULL,
                "plain_title" TEXT NOT NULL,
                "plain_snippet" TEXT NOT NULL,
                "image_url" TEXT,
                "enclosure_link" TEXT,
                "author" TEXT,
                "pub_date" TEXT,
                "link" TEXT,
                "unread" INTEGER NOT NULL,
                "notified" INTEGER NOT NULL,
                "feed_id" INTEGER,
                "image_count" INTEGER NOT NULL,
                "create_date" INTEGER NOT NULL,
                "item_tags" TEXT NOT NULL,
                "read_history" TEXT,
                "item_feature" INTEGER,
                FOREIGN KEY ("feed_id") REFERENCES "feeds" ("id") ON DELETE CASCADE ON UPDATE NO ACTION,
                FOREIGN KEY ("item_feature") REFERENCES "feed_item_feature" ("id") ON DELETE CASCADE ON UPDATE NO ACTION
         )
        """.trimIndent())
        database.execSQL("""
            INSERT INTO "feed_items" ("id", "guid", "title", "description", "plain_title", "plain_snippet", "image_url", "enclosure_link", "author", "pub_date", "link", "unread", "notified", "feed_id", "image_count", "create_date", "item_tags", "read_history") SELECT "id", "guid", "title", "description", "plain_title", "plain_snippet", "image_url", "enclosure_link", "author", "pub_date", "link", "unread", "notified", "feed_id", "image_count", "create_date", "item_tags", "read_history" FROM "_feed_items_old_20200513"
        """.trimIndent())
        database.execSQL("""
            CREATE INDEX "index_feed_items_feed_id" ON "feed_items" ( "feed_id" ASC )
        """.trimIndent())
        database.execSQL("""
           CREATE UNIQUE INDEX "index_feed_items_guid_feed_id" ON "feed_items" ("guid" ASC,"feed_id" ASC)
        """.trimIndent())
    }
}

object MIGRATION_13_14: Migration(13, 14) {
    override fun migrate(database: SupportSQLiteDatabase) {
        database.execSQL("""
            ALTER TABLE feeds ADD COLUMN new_item_count INTEGER NOT NULL DEFAULT 0
        """.trimIndent())
        database.execSQL("""
            CREATE TABLE IF NOT EXISTS `feed_sync_item` (`id` INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,create_date INTEGER NOT NULL DEFAULT 0, sync_success INTEGER NOT NULL DEFAULT 1 ,sync_time_interval INTEGER NOT NULL DEFAULT 0 ,new_item_count INTEGER NOT NULL DEFAULT 0 , `feed_id` INTEGER, FOREIGN KEY(`feed_id`) REFERENCES `feeds`(`id`) ON UPDATE NO ACTION ON DELETE CASCADE )
        """.trimIndent())
    }
}

object MIGRATION_12_13: Migration(12, 13) {
    override fun migrate(database: SupportSQLiteDatabase) {
        database.execSQL("""
            ALTER TABLE feed_items ADD COLUMN read_history TEXT
        """.trimIndent())
    }
}

object MIGRATION_11_12: Migration(11, 12) {
    override fun migrate(database: SupportSQLiteDatabase) {
        database.execSQL("""
            ALTER TABLE feed_items ADD COLUMN item_tags TEXT NOT NULL DEFAULT ""
        """.trimIndent())
    }
}

object MIGRATION_10_11: Migration(10, 11) {
    override fun migrate(database: SupportSQLiteDatabase) {
        database.execSQL("""
            ALTER TABLE feed_items ADD COLUMN create_date INTEGER NOT NULL DEFAULT 0
        """.trimIndent())
    }
}


@Suppress("ClassName")
/**
 * 9 represents legacy database
 * 10 represents new Room database
 */
object MIGRATION_9_10: Migration(9, 10) {
    override fun migrate(database: SupportSQLiteDatabase) {
        database.execSQL("""
            ALTER TABLE feeds ADD COLUMN feed_set_open_type INTEGER NOT NULL DEFAULT 0
        """.trimIndent())
        database.execSQL("""
            ALTER TABLE feeds ADD COLUMN feed_state_net_err INTEGER NOT NULL DEFAULT 0
        """.trimIndent())
        database.execSQL("""
            ALTER TABLE feeds ADD COLUMN feed_set_sync_able INTEGER NOT NULL DEFAULT 1
        """.trimIndent())
        database.execSQL("""
            ALTER TABLE feeds ADD COLUMN feed_set_sync_period INTEGER NOT NULL DEFAULT 0
        """.trimIndent())
        database.execSQL("""
            ALTER TABLE feed_items ADD COLUMN image_count INTEGER NOT NULL DEFAULT 0
        """.trimIndent())
    }
}

object MIGRATION_8_9: Migration(8, 9) {
    override fun migrate(database: SupportSQLiteDatabase) {
        database.execSQL("""
            ALTER TABLE feeds ADD COLUMN response_hash INTEGER NOT NULL DEFAULT 0
        """.trimIndent())
    }
}
object MIGRATION_7_8: Migration(7, 8) {
    override fun migrate(database: SupportSQLiteDatabase) {
        database.execSQL("""
            ALTER TABLE feeds ADD COLUMN last_sync INTEGER NOT NULL DEFAULT 0
        """.trimIndent())
    }
}
object MIGRATION_6_7 : Migration(6, 7) {
    override fun migrate(database: SupportSQLiteDatabase) {
        legacyMigration(database, 6)
    }
}

object MIGRATION_5_7 : Migration(5, 7) {
    override fun migrate(database: SupportSQLiteDatabase) {
        legacyMigration(database, 5)
    }
}

private fun legacyMigration(database: SupportSQLiteDatabase, version: Int) {
    // Create new tables and indices
    // Feeds
    database.execSQL("""
            CREATE TABLE IF NOT EXISTS `feeds` (`id` INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, `title` TEXT NOT NULL, `custom_title` TEXT NOT NULL, `url` TEXT NOT NULL, `tag` TEXT NOT NULL, `notify` INTEGER NOT NULL, `image_url` TEXT)
        """.trimIndent())
    database.execSQL("""
            CREATE UNIQUE INDEX `index_Feed_url` ON `feeds` (`url`)
        """.trimIndent())
    database.execSQL("""
            CREATE UNIQUE INDEX `index_Feed_id_url_title` ON `feeds` (`id`, `url`, `title`)
        """.trimIndent())

    // Items
    database.execSQL("""
            CREATE TABLE IF NOT EXISTS `feed_items` (`id` INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, `guid` TEXT NOT NULL, `title` TEXT NOT NULL, `description` TEXT NOT NULL, `plain_title` TEXT NOT NULL, `plain_snippet` TEXT NOT NULL, `image_url` TEXT, `enclosure_link` TEXT, `author` TEXT, `pub_date` TEXT, `link` TEXT, `unread` INTEGER NOT NULL, `notified` INTEGER NOT NULL, `feed_id` INTEGER, FOREIGN KEY(`feed_id`) REFERENCES `feeds`(`id`) ON UPDATE NO ACTION ON DELETE CASCADE )
            """.trimIndent())
    database.execSQL("""
            CREATE UNIQUE INDEX `index_feed_item_guid_feed_id` ON `feed_items` (`guid`, `feed_id`)
        """.trimIndent())
    database.execSQL("""
             CREATE  INDEX `index_feed_item_feed_id` ON `feed_items` (`feed_id`)
        """.trimIndent())

    // Migrate to new tables
    database.query("""
            SELECT _id, title, url, tag, customtitle, notify ${if (version == 6) ", imageUrl" else ""}
            FROM Feed
        """.trimIndent())?.use { cursor ->
        cursor.forEach { _ ->
            val oldFeedId = cursor.getLong(0)

            val newFeedId = database.insert("feeds",
                    SQLiteDatabase.CONFLICT_FAIL,
                    contentValues {
                        setString("title" to cursor.getString(1))
                        setString("custom_title" to cursor.getString(4))
                        setString("url" to cursor.getString(2))
                        setString("tag" to cursor.getString(3))
                        setInt("notify" to cursor.getInt(5))
                        if (version == 6) {
                            setString("image_url" to cursor.getString(6))
                        }
                    })

            database.query("""
                    SELECT title, description, plainTitle, plainSnippet, imageUrl, link, author,
                           pubdate, unread, feed, enclosureLink, notified, guid
                    FROM FeedItem
                    WHERE feed = $oldFeedId
                """.trimIndent())?.use { cursor ->
                database.inTransaction {
                    cursor.forEach { _ ->
                        database.insert("feed_items",
                                SQLiteDatabase.CONFLICT_FAIL,
                                contentValues {
                                    setString("guid" to cursor.getString(12))
                                    setString("title" to cursor.getString(0))
                                    setString("description" to cursor.getString(1))
                                    setString("plain_title" to cursor.getString(2))
                                    setString("plain_snippet" to cursor.getString(3))
                                    setString("image_url" to cursor.getString(4))
                                    setString("enclosure_link" to cursor.getString(10))
                                    setString("author" to  cursor.getString(6))
                                    setString("pub_date" to cursor.getString(7))
                                    setString("link" to  cursor.getString(5))
                                    setInt("unread" to cursor.getInt(8))
                                    setInt("notified" to cursor.getInt(11))
                                    setLong("feed_id" to newFeedId)
                                })
                    }
                }
            }
        }
    }

    // Remove all legacy content
    database.execSQL("DROP TRIGGER IF EXISTS trigger_tag_updater")

    database.execSQL("DROP VIEW IF EXISTS WithUnreadCount")
    database.execSQL("DROP VIEW IF EXISTS TagsWithUnreadCount")

    database.execSQL("DROP TABLE IF EXISTS Feed")
    database.execSQL("DROP TABLE IF EXISTS FeedItem")
}

fun SupportSQLiteDatabase.inTransaction(init: (SupportSQLiteDatabase) -> Unit) {
    beginTransaction()
    try {
        init(this)
        setTransactionSuccessful()
    } finally {
        endTransaction()
    }
}
